import os
import shutil
from sqlx import Sqlx, SqlxDatabaseError

# 测试 SQL 内容
TEST_SQL = """
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER NOT NULL
);

INSERT INTO users (name, age) VALUES ('Alice', 30);
INSERT INTO users (name, age) VALUES ('Bob', 25);
INSERT INTO users (name, age) VALUES ('Charlie', 35);
INSERT INTO users (name, age) VALUES ('David', 40);
INSERT INTO users (name, age) VALUES ('Eve', 45);
INSERT INTO users (name, age) VALUES ('Fred', 50);
INSERT INTO users (name, age) VALUES ('Greg', 55);
INSERT INTO users (name, age) VALUES ('Harry', 25);
"""


def create_test_db():
    # 创建 test.db 数据库并写入测试数据
    db_url = "sqlite:///data/test.db"
    sqlx = Sqlx(
        db_url, pool_size=10, max_overflow=20, pool_timeout=30, pool_recycle=3600, enable_log=True
    )
    script_path = "test.sql"
    with open(script_path, "w") as file:
        file.write(TEST_SQL)
    result = sqlx.exec_script(script_path)
    sqlx.close()


def test_connection():
    try:
        db_url = "sqlite:///data/test.db"
        sqlx = Sqlx(
            db_url,
            pool_size=10,
            max_overflow=20,
            pool_timeout=30,
            pool_recycle=3600,
            enable_log=True,
        )
        print("数据库连接成功")
        return sqlx
    except SqlxDatabaseError as e:
        print(f"数据库连接失败: {e}")
        return None


def test_newSqlx():
    try:
        sqlx = Sqlx.newSqlx(
            db_type="sqlite",
            host=None,
            port=None,
            user=None,
            password=None,
            db_name="data/test.db",
            pool_size=10,
            max_overflow=20,
            pool_timeout=30,
            pool_recycle=3600,
        )
        print("通过 newSqlx 创建的数据库连接成功")
        return sqlx
    except SqlxDatabaseError as e:
        print(f"通过 newSqlx 创建的数据库连接失败: {e}")
        return None


def test_query(sqlx):
    query_sql = "SELECT id, name, age FROM users WHERE age > :age"
    query_params = {"age": 25}
    result = sqlx.exec_sql(query_sql, query_params, True)
    if result is None:
        print("Error: result is None")
        return

    list_keys = ["id", "name", "age"]
    dict_results = list(result.to_dicts_iter(list_keys))
    print(f"查询结果字典: {', '.join(str(ob) for ob in dict_results)}")
    print(f"查询结果json: {result.to_jsons(list_keys)}")
    # 将生成器转换为列表以便打印
    results_list = list(result.results_iter())
    print(f"查询结果生成器: {results_list}")
    assert result.is_success(), "查询操作失败"


def test_insert(sqlx):
    insert_sql = "INSERT INTO users (name, age) VALUES (:name, :age)"
    insert_params = {"name": "Charlie", "age": 35}
    result = sqlx.exec_sql(insert_sql, insert_params)
    print(f"插入结果: {result}")
    assert result.is_success(), "插入操作失败"


def test_update(sqlx):
    update_sql = "UPDATE users SET age = :age WHERE name = :name"
    update_params = {"name": "Alice", "age": 31}
    result = sqlx.exec_sql(update_sql, update_params)
    print(f"更新结果: {result}")
    assert result.is_success(), "更新操作失败"


def test_delete(sqlx):
    delete_sql = "DELETE FROM users WHERE name = :name"
    delete_params = {"name": "Charlie"}
    result = sqlx.exec_sql(delete_sql, delete_params)
    print(f"删除结果: {result}")
    assert result.is_success(), "删除操作失败"


def test_exec_script(sqlx):
    script_path = "test.sql"
    with open(script_path, "w") as file:
        file.write(TEST_SQL)
    result = sqlx.exec_script(script_path)
    print(f"执行 SQL 脚本结果: {result}")
    assert result.is_success(), "执行 SQL 脚本失败"
    os.remove(script_path)  # 删除测试脚本文件


def test_close(sqlx):
    try:
        sqlx.close()
        print("关闭数据库连接成功")
    except SqlxDatabaseError as e:
        print(f"关闭数据库连接失败: {e}")

def test_te(sqlx):
    try:
        sqlx.exec_script("tt.sql")
    except Exception as e:
        print(f"执行 SQL 脚本失败: {e}")


# 测试获取连接信息
def test_get_connection_info(sqlx):
    connection_info = sqlx.get_connection_info()
    print(f"数据库连接信息: {connection_info}")
    assert connection_info is not None, "获取连接信息失败"


def main():
    # 确保 data 目录存在
    if not os.path.exists("data"):
        os.makedirs("data")

    # 创建 test.db 数据库并写入测试数据
    create_test_db()

    # 测试通过直接连接字符串创建 Sqlx 实例
    sqlx = test_connection()
    if sqlx:
        test_query(sqlx)
        test_insert(sqlx)
        test_update(sqlx)
        test_delete(sqlx)
        test_exec_script(sqlx)
        test_te(sqlx)
        test_close(sqlx)

    # 测试通过 newSqlx 方法创建 Sqlx 实例
    # new_sqlx = test_newSqlx()
    # if new_sqlx:
    #     test_query(new_sqlx)
    #     test_insert(new_sqlx)
    #     test_update(new_sqlx)
    #     test_delete(new_sqlx)
    #     test_exec_script(new_sqlx)
    #     test_get_connection_info(new_sqlx)
    #     test_close(new_sqlx)

    try:
        # 清理测试产生的数据和目录
        if os.path.exists("data/test.db"):
            os.remove("data/test.db")
        if os.path.exists("data"):
            os.rmdir("data")
        if os.path.exists("test.sql"):
            os.remove("test.sql")
        if os.path.exists("__pycache__"):
            shutil.rmtree("__pycache__")
    except Exception as e:
        print(f"清理测试数据失败: {e}")


if __name__ == "__main__":
    main()
    print("测试完成")
